﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using OgilvyOne.IDAL;
using OgilvyOne.Model;
using OgilvyOne.Utility;

namespace OgilvyOne.SqlServerDAL
{
    public class MemberInfo : IMemberInfo
    {
        public const string COFCO_MEMBER_TYPE = "1";
        private const string ADD_SQL = "insert into COFCO_MEMBER_INFO(USERNAME,MEMBER_NICKNAME,PASSWORD,MEMBER_NAME,USER_TYPE,TEL,MOBLIE,POSTCODE,ADDRESS,REG_TIME,IP,LAST_LOGIN_TIME,STATE,MEMBER_IMG,EMAIL) values(@USERNAME,@MEMBER_NICKNAME,@PASSWORD,@MEMBER_NAME,@USER_TYPE,@TEL,@MOBLIE,@POSTCODE,@ADDRESS,getDate(),@IP,getDate(),1,@MEMBER_IMG,@EMAIL);select @@IDENTITY";
        //添加
        public int Add(MemberInfoModel model)
        {
            
            SqlParameter[] parms = new SqlParameter[] { 
                new SqlParameter("@USERNAME", SqlDbType.VarChar,200),
                new SqlParameter("@MEMBER_NICKNAME",SqlDbType.VarChar,100),
                new SqlParameter("@PASSWORD",SqlDbType.VarChar,50), 
                new SqlParameter("@MEMBER_NAME",SqlDbType.VarChar,50),
                new SqlParameter("@USER_TYPE",SqlDbType.VarChar,20),
                new SqlParameter("@TEL", SqlDbType.VarChar, 20),
                new SqlParameter("@MOBLIE",SqlDbType.VarChar,20),
                new SqlParameter("@POSTCODE",SqlDbType.VarChar,10),
                new SqlParameter("@ADDRESS",SqlDbType.VarChar,200),
                new SqlParameter("@IP", SqlDbType.VarChar, 50),
                new SqlParameter("@MEMBER_IMG", SqlDbType.VarChar, 50),
                new SqlParameter("@EMAIL", SqlDbType.VarChar, 200)
            };
            parms[0].Value = model.Username;
            parms[1].Value = model.MemberNickname;
            parms[2].Value = model.Password;
            parms[3].Value = model.Membername;
            parms[4].Value = model.UserType;
            parms[5].Value = model.Tel;
            parms[6].Value = model.Moblie;
            parms[7].Value = model.Postcode;
            parms[8].Value = model.Address;
            parms[9].Value = model.Ip;
            parms[10].Value = model.Userimg;
            parms[11].Value = model.Email;

            int id = 0;
            using (SqlConnection conn = new SqlConnection(SQLHelper.CONN_STRING_NON_DTC))
            {
                conn.Open();
                using (SqlTransaction trans = conn.BeginTransaction())
                {
                    try
                    {
                        string s = SQLHelper.ExecuteScalar(trans, CommandType.Text, ADD_SQL, parms).ToString();
                        id = int.Parse(s);
                        trans.Commit();
                    }
                    catch
                    {
                        trans.Rollback();
                        throw;
                    }
                }
            }

            return id;
        }
       
        private const string UPDATE_SQL = "update COFCO_MEMBER_INFO set BIRTHDAY=@BIRTHDAY,MEMBER_NAME=@MEMBER_NAME,TEL=@TEL,MOBLIE=@MOBLIE,POSTCODE=@POSTCODE,ADDRESS=@ADDRESS,SEX=@SEX,CITY=@CITY,INTEREST=@INTEREST,LOVE_CONTENT=@LOVE_CONTENT where MEMBER_ID=@MEMBER_ID";
        //修改基本资料
        public bool Update(MemberInfoModel model)
        {
            SqlParameter[] parms = new SqlParameter[] { 
                new SqlParameter("@BIRTHDAY",SqlDbType.VarChar,100),
                new SqlParameter("@MEMBER_NAME",SqlDbType.VarChar,50),
                new SqlParameter("@TEL", SqlDbType.VarChar, 20),
                new SqlParameter("@MOBLIE",SqlDbType.VarChar,20),
                new SqlParameter("@POSTCODE",SqlDbType.VarChar,10),
                new SqlParameter("@ADDRESS",SqlDbType.VarChar,200),
                new SqlParameter("@MEMBER_ID", SqlDbType.Int, 4),
                new SqlParameter("@SEX", SqlDbType.VarChar, 2),
                new SqlParameter("@CITY", SqlDbType.VarChar, 100),
                new SqlParameter("@INTEREST", SqlDbType.VarChar, 200),
                new SqlParameter("@LOVE_CONTENT", SqlDbType.VarChar, 200),
            };
            
            parms[0].Value = model.Birthday;
            parms[1].Value = model.Membername;
            parms[2].Value = model.Tel;
            parms[3].Value = model.Moblie;
            parms[4].Value = model.Postcode;
            parms[5].Value = model.Address;
            parms[6].Value = model.Memberid;
            parms[7].Value = model.Sex;
            parms[8].Value = model.City;
            parms[9].Value = model.Interst;
            parms[10].Value = model.LoveContent;

            int i = SQLHelper.ExecuteSql(UPDATE_SQL, parms);
            if (i > 0) return true;
            return false;
        }




        //删除会员信息
        public bool Delete(int memberid)
        {
            SqlParameter[] parms = new SqlParameter[] { 
            };
            string Delete_SQL1 = "delete from COFCO_MEMBER_INFO where MEMBER_ID = "+memberid.ToString();
            int i = SQLHelper.ExecuteSql(Delete_SQL1, parms);
            string Delete_SQL2 = "delete from CMS_ARTICLE_COMMENTS where member_id = " + memberid.ToString();
            int k = SQLHelper.ExecuteSql(Delete_SQL2, parms);
            string Delete_SQL3 = "delete from COFCO_ACTIVITY_MESSAGES where member_id = " + memberid.ToString();
            int l = SQLHelper.ExecuteSql(Delete_SQL3, parms);
            string Delete_SQL4 = "delete from COFCO_ARTICLE_COLLECTION where member_id = " + memberid.ToString();
            int m = SQLHelper.ExecuteSql(Delete_SQL4, parms);
            return true;
        }


        //修改登录时间
        public bool UpdateLastLoginTime(int mid, DateTime logintime)
        {
            SqlParameter[] para = new SqlParameter[] { 
                new SqlParameter("@LAST_LOGIN_TIME",SqlDbType.DateTime)
            };
            para[0].Value = logintime;
            int i = SQLHelper.ExecuteSql("update COFCO_MEMBER_INFO set LAST_LOGIN_TIME=@LAST_LOGIN_TIME where MEMBER_ID=" + mid, para);
            if (i > 0) return true;
            return false;
        }
        //修改密码
        public bool UpdatePassword(int mid, string newpas)
        {
            int i = SQLHelper.ExecuteSql("update COFCO_MEMBER_INFO set PASSWORD='" + newpas + "' where MEMBER_ID=" + mid, new SqlParameter[]{});
            if (i > 0) return true;
            return false;
        }

        //修改头像
        public bool UpdateUserImg(int mid, string userimg)
        {
            int i = SQLHelper.ExecuteSql("update COFCO_MEMBER_INFO set MEMBER_IMG='" + userimg + "' where MEMBER_ID=" + mid, new SqlParameter[] { });
            if (i > 0) return true;
            return false;
        }

        //修改状态
        public bool UpdateState(int mid, int state)
        {
            int i = SQLHelper.ExecuteSql("update COFCO_MEMBER_INFO set STATE=" + state + " where MEMBER_ID=" + mid, new SqlParameter[] { });
            if (i > 0) return true;
            return false;
        }
        private const string GET_MEMBER_SQL = "select MEMBER_ID,USERNAME,MEMBER_NICKNAME,PASSWORD,MEMBER_NAME,USER_TYPE,TEL,MOBLIE,POSTCODE,ADDRESS,REG_TIME,IP,LAST_LOGIN_TIME,STATE,EMAIL,SEX,BIRTHDAY,CITY,INTEREST,LOVE_CONTENT,MEMBER_IMG from COFCO_MEMBER_INFO where USERNAME=@USERNAME and USER_TYPE=@USER_TYPE";
        public MemberInfoModel GetMemberInfoModel(string username)
        {
            return GetMemberInfoModel(username, COFCO_MEMBER_TYPE);//官网会员
        }
        //获得用户信息
        public MemberInfoModel GetMemberInfoModel(string username, string userType)
        {
            MemberInfoModel mim = null;
            SqlParameter[] parms = new SqlParameter[] { 
                new SqlParameter("@USERNAME", SqlDbType.VarChar, 50),
                new SqlParameter("@USER_TYPE",SqlDbType.VarChar, 20)
            };
            parms[0].Value = username;
            parms[1].Value = userType;
            using (SqlDataReader rdr = SQLHelper.ExecuteReader(SQLHelper.CONN_STRING_NON_DTC, CommandType.Text, GET_MEMBER_SQL, parms))
            {
                if (rdr.Read())
                {
                    mim = new MemberInfoModel();

                    mim.Memberid = rdr.GetInt32(0);
                    mim.Username = rdr.GetString(1);
                    mim.MemberNickname = rdr.GetString(2);
                    mim.Password = rdr.GetString(3);
                    if (!rdr.IsDBNull(4)) mim.Membername = rdr.GetString(4);
                    if (!rdr.IsDBNull(5)) mim.UserType = rdr.GetString(5);
                    if (!rdr.IsDBNull(6)) mim.Tel = rdr.GetString(6);
                    if (!rdr.IsDBNull(7)) mim.Moblie = rdr.GetString(7);
                    if (!rdr.IsDBNull(8)) mim.Postcode = rdr.GetString(8);
                    if (!rdr.IsDBNull(9)) mim.Address = rdr.GetString(9);
                    if (!rdr.IsDBNull(10)) mim.RegTime = rdr.GetDateTime(10);
                    if (!rdr.IsDBNull(11)) mim.Ip = rdr.GetString(11);
                    if (!rdr.IsDBNull(12)) mim.LastLoginTime = rdr.GetDateTime(12);
                    if (!rdr.IsDBNull(13)) mim.State = rdr.GetInt32(13);
                    if (!rdr.IsDBNull(14)) mim.Email = rdr.GetString(14);
                    if (!rdr.IsDBNull(15)) mim.Sex = rdr.GetString(15);
                    if (!rdr.IsDBNull(16)) mim.Birthday = rdr.GetString(16);
                    if (!rdr.IsDBNull(17)) mim.City = rdr.GetString(17);
                    if (!rdr.IsDBNull(18)) mim.Interst = rdr.GetString(18);
                    if (!rdr.IsDBNull(19)) mim.LoveContent = rdr.GetString(19);
                    if (!rdr.IsDBNull(20)) mim.Userimg = rdr.GetString(20);
                }
            }
            return mim;
        }


        private const string GET_MEMBER_EMAIL_SQL = "select MEMBER_ID,USERNAME,MEMBER_NICKNAME,PASSWORD,MEMBER_NAME,USER_TYPE,TEL,MOBLIE,POSTCODE,ADDRESS,REG_TIME,IP,LAST_LOGIN_TIME,STATE,EMAIL,SEX,BIRTHDAY,CITY,INTEREST,LOVE_CONTENT,MEMBER_IMG from COFCO_MEMBER_INFO where USER_TYPE='1' and EMAIL=@EMAIL";
        /// <summary>
        /// 根据用户邮箱获取用户信息
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public MemberInfoModel GetMemberInfoModelEmail(string email)
        {
            MemberInfoModel mim = null;
            SqlParameter[] parms = new SqlParameter[] { 
                new SqlParameter("@EMAIL", SqlDbType.VarChar, 200)
                
            };
            parms[0].Value = email;

            using (SqlDataReader rdr = SQLHelper.ExecuteReader(SQLHelper.CONN_STRING_NON_DTC, CommandType.Text, GET_MEMBER_EMAIL_SQL, parms))
            {
                if (rdr.Read())
                {
                    mim = new MemberInfoModel();

                    mim.Memberid = rdr.GetInt32(0);
                    mim.Username = rdr.GetString(1);
                    mim.MemberNickname = rdr.GetString(2);
                    mim.Password = rdr.GetString(3);
                    if (!rdr.IsDBNull(4)) mim.Membername = rdr.GetString(4);
                    if (!rdr.IsDBNull(5)) mim.UserType = rdr.GetString(5);
                    if (!rdr.IsDBNull(6)) mim.Tel = rdr.GetString(6);
                    if (!rdr.IsDBNull(7)) mim.Moblie = rdr.GetString(7);
                    if (!rdr.IsDBNull(8)) mim.Postcode = rdr.GetString(8);
                    if (!rdr.IsDBNull(9)) mim.Address = rdr.GetString(9);
                    if (!rdr.IsDBNull(10)) mim.RegTime = rdr.GetDateTime(10);
                    if (!rdr.IsDBNull(11)) mim.Ip = rdr.GetString(11);
                    if (!rdr.IsDBNull(12)) mim.LastLoginTime = rdr.GetDateTime(12);

                    if (!rdr.IsDBNull(13)) mim.State = rdr.GetInt32(13);
                    if (!rdr.IsDBNull(14)) mim.Email = rdr.GetString(14);
                    if (!rdr.IsDBNull(15)) mim.Sex = rdr.GetString(15);
                    if (!rdr.IsDBNull(16)) mim.Birthday = rdr.GetString(16);
                    if (!rdr.IsDBNull(17)) mim.City = rdr.GetString(17);
                    if (!rdr.IsDBNull(18)) mim.Interst = rdr.GetString(18);
                    if (!rdr.IsDBNull(19)) mim.LoveContent = rdr.GetString(19);
                    if (!rdr.IsDBNull(20)) mim.Userimg = rdr.GetString(20);
                }
            }
            return mim;
        }
        private const string GET_MEMBER_ID_SQL = "select MEMBER_ID,USERNAME,MEMBER_NICKNAME,PASSWORD,MEMBER_NAME,USER_TYPE,TEL,MOBLIE,POSTCODE,ADDRESS,REG_TIME,IP,LAST_LOGIN_TIME,STATE,EMAIL,SEX,BIRTHDAY,CITY,INTEREST,LOVE_CONTENT,MEMBER_IMG from COFCO_MEMBER_INFO where MEMBER_ID=@MEMBER_ID";
        //获得用户信息
        public MemberInfoModel GetMemberInfoModel(int mid) 
        {
            MemberInfoModel mim = null;
            SqlParameter[] parms = new SqlParameter[] { 
                new SqlParameter("@MEMBER_ID", SqlDbType.Int, 4)
                
            };
            parms[0].Value = mid;
            
            using (SqlDataReader rdr = SQLHelper.ExecuteReader(SQLHelper.CONN_STRING_NON_DTC, CommandType.Text, GET_MEMBER_ID_SQL, parms))
            {
                if (rdr.Read())
                {
                    mim = new MemberInfoModel();

                    mim.Memberid = rdr.GetInt32(0);
                    mim.Username = rdr.GetString(1);
                    mim.MemberNickname = rdr.GetString(2);
                    mim.Password = rdr.GetString(3);
                    if (!rdr.IsDBNull(4)) mim.Membername = rdr.GetString(4);
                    if (!rdr.IsDBNull(5)) mim.UserType = rdr.GetString(5);
                    if (!rdr.IsDBNull(6)) mim.Tel = rdr.GetString(6);
                    if (!rdr.IsDBNull(7)) mim.Moblie = rdr.GetString(7);
                    if (!rdr.IsDBNull(8)) mim.Postcode = rdr.GetString(8);
                    if (!rdr.IsDBNull(9)) mim.Address = rdr.GetString(9);
                    if (!rdr.IsDBNull(10)) mim.RegTime = rdr.GetDateTime(10);
                    if (!rdr.IsDBNull(11)) mim.Ip = rdr.GetString(11);
                    if (!rdr.IsDBNull(12)) mim.LastLoginTime = rdr.GetDateTime(12);

                    if (!rdr.IsDBNull(13)) mim.State = rdr.GetInt32(13);
                    if (!rdr.IsDBNull(14)) mim.Email = rdr.GetString(14);
                    if (!rdr.IsDBNull(15)) mim.Sex = rdr.GetString(15);
                    if (!rdr.IsDBNull(16)) mim.Birthday = rdr.GetString(16);
                    if (!rdr.IsDBNull(17)) mim.City = rdr.GetString(17);
                    if (!rdr.IsDBNull(18)) mim.Interst = rdr.GetString(18);
                    if (!rdr.IsDBNull(19)) mim.LoveContent = rdr.GetString(19);
                    if (!rdr.IsDBNull(20)) mim.Userimg = rdr.GetString(20);
                }
            }
            return mim;
        }
        public bool IsExists(string username)
        {
            return IsExists(username, COFCO_MEMBER_TYPE);
        }
        //是否存在
        public bool IsExists(string username, string userType)
        {
            if (GetMemberInfoModel(username, userType) != null)
                return true;
            else
                return false;
        }
        //查询会员
        public IList GetUserList(MemberInfoModel q)
        {
            string qsql = "select MEMBER_ID,USERNAME,MEMBER_NICKNAME,PASSWORD,MEMBER_NAME,USER_TYPE,TEL,MOBLIE,POSTCODE,ADDRESS,REG_TIME,IP,LAST_LOGIN_TIME,State from COFCO_MEMBER_INFO where 1=1 ";
            
            if (q != null)
            {
                if (q.Username != null && q.Username != "")
                {
                    qsql += " and username like '%" + StringUtil.ReplaceSecStr(q.Username) + "%'";
                }
            }
            qsql += " order by reg_time desc";
            IList datalist = new ArrayList();
            SqlParameter[] parms = new SqlParameter[] { };

            using (SqlDataReader rdr = SQLHelper.ExecuteReader(SQLHelper.CONN_STRING_NON_DTC, CommandType.Text, qsql, parms))
            {
                while (rdr.Read())
                {
                    MemberInfoModel mim = new MemberInfoModel();

                    mim.Memberid = rdr.GetInt32(0);
                    mim.Username = rdr.GetString(1);
                    mim.MemberNickname = rdr.GetString(2);
                    mim.Password = rdr.GetString(3);
                    if (!rdr.IsDBNull(4)) mim.Membername = rdr.GetString(4);
                    if (!rdr.IsDBNull(5)) mim.UserType = rdr.GetString(5);
                    if (!rdr.IsDBNull(6)) mim.Tel = rdr.GetString(6);
                    if (!rdr.IsDBNull(7)) mim.Moblie = rdr.GetString(7);
                    if (!rdr.IsDBNull(8)) mim.Postcode = rdr.GetString(8);
                    if (!rdr.IsDBNull(9)) mim.Address = rdr.GetString(9);
                    if (!rdr.IsDBNull(10)) mim.RegTime = rdr.GetDateTime(10);
                    if (!rdr.IsDBNull(11)) mim.Ip = rdr.GetString(11);
                    if (!rdr.IsDBNull(12)) mim.LastLoginTime = rdr.GetDateTime(12);
                    if (!rdr.IsDBNull(13)) mim.State = rdr.GetInt32(13);
                    datalist.Add(mim);

                }
            }
            return datalist;
        }

        /// <summary>
        /// 查询收藏列表
        /// </summary>
        /// <returns></returns>
        public IList GetColl(int mid,int top)
        {
            string sql = "select";
            if (top != 0)
            {
                sql += " top " + top;
            }
            sql += " ca.TITLE,ca.ARTICLEID,cac.ADD_TIME,cac.COLLECTION_ID from COFCO_ARTICLE_COLLECTION cac, COFCO_MEMBER_INFO cmi, CMS_ARTICLE ca where cac.MEMBER_ID=cmi.MEMBER_ID and cac.ARTICLE_ID=ca.ARTICLEID and cac.MEMBER_ID= " + mid + " order by cac.ADD_TIME desc ";
            IList datalist = new ArrayList();
            SqlParameter[] parms = new SqlParameter[] { };

            using (SqlDataReader rdr = SQLHelper.ExecuteReader(SQLHelper.CONN_STRING_NON_DTC, CommandType.Text, sql, parms))
            {
                while (rdr.Read())
                {
                    MemberInfoModel mim = new MemberInfoModel();
                    if (!rdr.IsDBNull(0)) mim.CollTitle = rdr.GetString(0);
                    if (!rdr.IsDBNull(1)) mim.CollAid = rdr.GetInt32(1);
                    if (!rdr.IsDBNull(2)) mim.CollTime = rdr.GetDateTime(2);
                    mim.Cid = rdr.GetInt32(3);
                    datalist.Add(mim);

                }
            }
            return datalist;
        }
        /// <summary>
        /// 查询收藏列表
        /// </summary>
        /// <returns></returns>
        //and ca.columnid= "+cid+"
        public IList GetColl(int mid,string cid)
        {
            string sql = "select ca.TITLE,ca.ARTICLEID,cac.ADD_TIME,cac.COLLECTION_ID from COFCO_ARTICLE_COLLECTION cac, COFCO_MEMBER_INFO cmi, CMS_ARTICLE ca where cac.MEMBER_ID=cmi.MEMBER_ID and cac.ARTICLE_ID=ca.ARTICLEID and cac.MEMBER_ID= " + mid + "and ca.columnid= " + cid + "  order by cac.ADD_TIME desc ";
            IList datalist = new ArrayList();
            SqlParameter[] parms = new SqlParameter[] { };

            using (SqlDataReader rdr = SQLHelper.ExecuteReader(SQLHelper.CONN_STRING_NON_DTC, CommandType.Text, sql, parms))
            {
                while (rdr.Read())
                {
                    MemberInfoModel mim = new MemberInfoModel();
                    if (!rdr.IsDBNull(0)) mim.CollTitle = rdr.GetString(0);
                    if (!rdr.IsDBNull(1)) mim.CollAid = rdr.GetInt32(1);
                    if (!rdr.IsDBNull(2)) mim.CollTime = rdr.GetDateTime(2);
                    mim.Cid = rdr.GetInt32(3);
                    datalist.Add(mim);

                }
            }
            return datalist;
        }
       
         /// <summary>
        /// 查询留言列表
        /// </summary>
        /// <returns></returns>
        public IList GetComments(int mid,int top)
        {
            string sql = "select ";
             if (top != 0)
            {
                sql += " top " + top;
            }
            sql+=" * from (select COMMENT_ID,ARTICLE_ID,COMMENT_CONTENT,MEMBER_ID,COMMENT_TIME,STATE,AUDIT_TIME,AUDIT_USERNAME,AUDIT_STATE,0 as type from CMS_ARTICLE_COMMENTS "
               +" union "
               +" select MESSAGE_ID,ACTIVITY_ID,MESSAGE_CONTENT,MEMBER_ID,MESSAGE_TIME,IS_VIEW,null,null,0,1 from COFCO_ACTIVITY_MESSAGES) as m"
                + " where m.MEMBER_ID=" + mid + " order by m.COMMENT_TIME desc";
            IList datalist = new ArrayList();
            SqlParameter[] parms = new SqlParameter[] { };

            using (SqlDataReader rdr = SQLHelper.ExecuteReader(SQLHelper.CONN_STRING_NON_DTC, CommandType.Text, sql, parms))
            {
                while (rdr.Read())
                {
                    CmsArticleCommentModel cacm = new CmsArticleCommentModel();
                    if (!rdr.IsDBNull(0)) cacm.CommentId=rdr.GetInt32(0);
                    if (!rdr.IsDBNull(1)) cacm.ArticleId=rdr.GetInt32(1);
                    if (!rdr.IsDBNull(2)) cacm.CommentContent = rdr.GetString(2);
                    if (!rdr.IsDBNull(3)) cacm.MemberId = rdr.GetInt32(3);
                    if (!rdr.IsDBNull(4)) cacm.CommentTime = rdr.GetDateTime(4);
                    if (!rdr.IsDBNull(5)) cacm.State = rdr.GetInt32(5);
                    if (!rdr.IsDBNull(6)) cacm.AuditTime = rdr.GetDateTime(6);
                    if (!rdr.IsDBNull(7)) cacm.AuditUsername = rdr.GetString(7);
                    if (!rdr.IsDBNull(8)) cacm.AuditState = rdr.GetInt32(8);
                    cacm.Type = rdr.GetInt32(9);
                    datalist.Add(cacm);

                }
            }
            return datalist;
        }
        /// <summary>
        /// 查询留言列表
        /// </summary>
        /// <returns></returns>
        public IList GetComments(int mid,string auditstate)
        {
            if (string.IsNullOrEmpty(auditstate)) { auditstate = "1"; }
            string sql = "select * from (select COMMENT_ID,ARTICLE_ID,COMMENT_CONTENT,MEMBER_ID,COMMENT_TIME,STATE,AUDIT_TIME,AUDIT_USERNAME,AUDIT_STATE,0 as type from CMS_ARTICLE_COMMENTS "
               + " union "
               + " select MESSAGE_ID,ACTIVITY_ID,MESSAGE_CONTENT,MEMBER_ID,MESSAGE_TIME,IS_VIEW,null,null,0,1 from COFCO_ACTIVITY_MESSAGES) as m"
                + " where m.MEMBER_ID=" + mid + " and m.STATE=" + auditstate + " order by m.COMMENT_TIME desc";
            IList datalist = new ArrayList();
            SqlParameter[] parms = new SqlParameter[] { };

            using (SqlDataReader rdr = SQLHelper.ExecuteReader(SQLHelper.CONN_STRING_NON_DTC, CommandType.Text, sql, parms))
            {
                while (rdr.Read())
                {
                    CmsArticleCommentModel cacm = new CmsArticleCommentModel();
                    if (!rdr.IsDBNull(0)) cacm.CommentId = rdr.GetInt32(0);
                    if (!rdr.IsDBNull(1)) cacm.ArticleId = rdr.GetInt32(1);
                    if (!rdr.IsDBNull(2)) cacm.CommentContent = rdr.GetString(2);
                    if (!rdr.IsDBNull(3)) cacm.MemberId = rdr.GetInt32(3);
                    if (!rdr.IsDBNull(4)) cacm.CommentTime = rdr.GetDateTime(4);
                    if (!rdr.IsDBNull(5)) cacm.State = rdr.GetInt32(5);
                    if (!rdr.IsDBNull(6)) cacm.AuditTime = rdr.GetDateTime(6);
                    if (!rdr.IsDBNull(7)) cacm.AuditUsername = rdr.GetString(7);
                    if (!rdr.IsDBNull(8)) cacm.AuditState = rdr.GetInt32(8);
                    cacm.Type = rdr.GetInt32(9);
                    datalist.Add(cacm);

                }
            }
            return datalist;
        }
        /// <summary>
        /// 查询留言总数
        /// </summary>
        /// <returns></returns>
        public IList GetCount(int mid,string auditstate)
        {
            string sql = "select count(*) cout from (select COMMENT_ID,ARTICLE_ID,COMMENT_CONTENT,MEMBER_ID,COMMENT_TIME,STATE,AUDIT_TIME,AUDIT_USERNAME,AUDIT_STATE,0 as type from CMS_ARTICLE_COMMENTS "
               + " union "
               + " select MESSAGE_ID,ACTIVITY_ID,MESSAGE_CONTENT,MEMBER_ID,MESSAGE_TIME,IS_VIEW,null,null,0,1 from COFCO_ACTIVITY_MESSAGES) as m where member_id=" + mid + " and state=" + auditstate;
            IList datalist = new ArrayList();
            SqlParameter[] parms = new SqlParameter[] { };
            using (SqlDataReader rdr = SQLHelper.ExecuteReader(SQLHelper.CONN_STRING_NON_DTC, CommandType.Text, sql, parms))
            {
                if (rdr.Read())
                {
                    CmsArticleCommentModel cacm = new CmsArticleCommentModel();
                    if (!rdr.IsDBNull(0)) cacm.Cout = rdr.GetInt32(0);
                    datalist.Add(cacm);
                }
            }
            return datalist;
        }
        /// <summary>
        /// 查询收藏总数
        /// </summary>
        /// <returns></returns>
        public IList GetcollCount(int mid)
        {
            string sql = "select ca.COLUMNID,cc.CLASSNAME, count(*) cout from COFCO_ARTICLE_COLLECTION cac,CMS_ARTICLE ca,cms_column cc where cac.ARTICLE_ID=ca.ARTICLEID  and cac.MEMBER_ID= "+mid+" and  ca.columnid=cc.classid group by ca.COLUMNID,cc.CLASSNAME order by COLUMNID";
            IList datalist = new ArrayList();
            SqlParameter[] parms = new SqlParameter[] { };
            using (SqlDataReader rdr = SQLHelper.ExecuteReader(SQLHelper.CONN_STRING_NON_DTC, CommandType.Text, sql, parms))
            {
                while (rdr.Read())
                {
                    MemberInfoModel cacm = new MemberInfoModel();
                    if (!rdr.IsDBNull(0)) cacm.Cid = rdr.GetInt32(0);
                    if (!rdr.IsDBNull(1)) cacm.Cname = rdr.GetString(1);
                    if (!rdr.IsDBNull(2)) cacm.Cout = rdr.GetInt32(2);
                    datalist.Add(cacm);
                }
            }
            return datalist;
        }
        /// <summary>
        /// 查询相册总数
        /// </summary>
        /// <returns></returns>
        public IList GetTitbitsCount(int mid, string state)
        {
            string sql = "select count(*) from COFCO_ACTIVITY_PHOTOS where MEMBER_ID="+mid+" and state="+state+"";
            IList datalist = new ArrayList();
            SqlParameter[] parms = new SqlParameter[] { };
            using (SqlDataReader rdr = SQLHelper.ExecuteReader(SQLHelper.CONN_STRING_NON_DTC, CommandType.Text, sql, parms))
            {
                while (rdr.Read())
                {
                    Hashtable ht = new Hashtable();
                    if (!rdr.IsDBNull(0)) ht["cout"] = rdr.GetInt32(0);
                    datalist.Add(ht);
                }
            }
            return datalist;
        }
        /// <summary>
        /// 查询相册列表
        /// </summary>
        /// <returns></returns>
        public IList GetTitbits(int mid, string state)
        {
            string sql = "select cap.ACTIVITY_ID,ca.ACTIVITY_NAME,TITLE_IMG,count(*),ca.START_DATE,ca.END_DATE from COFCO_ACTIVITY_PHOTOS cap,COFCO_ACTIVITY ca where cap.ACTIVITY_ID = ca.ACTIVITY_ID and MEMBER_ID=" + mid + " and ca.state=1 and cap.state=" + state + " group by cap.ACTIVITY_ID,ca.ACTIVITY_NAME,TITLE_IMG,ca.START_DATE,ca.END_DATE";
            IList datalist = new ArrayList();
            SqlParameter[] parms = new SqlParameter[] { };
            using (SqlDataReader rdr = SQLHelper.ExecuteReader(SQLHelper.CONN_STRING_NON_DTC, CommandType.Text, sql, parms))
            {
                while (rdr.Read())
                {
                    Hashtable ht = new Hashtable();
                    if (!rdr.IsDBNull(0)) ht["aid"] = rdr.GetInt32(0);
                    if (!rdr.IsDBNull(1)) ht["aname"] = rdr.GetString(1);
                    if (!rdr.IsDBNull(2)) ht["titleimg"] = rdr.GetString(2);
                    if (!rdr.IsDBNull(3)) ht["cout"] = rdr.GetInt32(3);
                    if (!rdr.IsDBNull(4)) ht["start"] = rdr.GetDateTime(4);
                    if (!rdr.IsDBNull(5)) ht["end"] = rdr.GetDateTime(5);
                    datalist.Add(ht);
                }
            }
            return datalist;
        }
        /// <summary>
        /// 查询照片列表
        /// </summary>
        /// <returns></returns>
        public IList GetPhoto(int mid, string state,int aid)
        {
            string sql = "select cap.ACTIVITY_ID,ca.ACTIVITY_NAME,cap.photo_img_file,cap.addtime from COFCO_ACTIVITY_PHOTOS cap,COFCO_ACTIVITY ca where cap.ACTIVITY_ID = ca.ACTIVITY_ID and MEMBER_ID=" + mid + " and ca.state=1 and cap.state=" + state + " and ca.activity_id=" + aid + "";
            IList datalist = new ArrayList();
            SqlParameter[] parms = new SqlParameter[] { };
            using (SqlDataReader rdr = SQLHelper.ExecuteReader(SQLHelper.CONN_STRING_NON_DTC, CommandType.Text, sql, parms))
            {
                while (rdr.Read())
                {
                    Hashtable ht = new Hashtable();
                    if (!rdr.IsDBNull(0)) ht["aid"] = rdr.GetInt32(0);
                    if (!rdr.IsDBNull(1)) ht["aname"] = rdr.GetString(1);
                    if (!rdr.IsDBNull(2)) ht["photofile"] = rdr.GetString(2);
                    if (!rdr.IsDBNull(3)) ht["adtime"] = rdr.GetDateTime(3);
                    datalist.Add(ht);
                }
            }
            return datalist;
        }
        static string coupon = "";
        //获取我买优惠码
        public string[] GetCoupon(int memberid)
        {
            string[] co = null;
            lock (coupon)
            {
                string sql = "select top 1 womai_code,password from womai_coupon where isapply=0";
                SqlParameter[] parms = new SqlParameter[] { };
                using (SqlDataReader rdr = SQLHelper.ExecuteReader(SQLHelper.CONN_STRING_NON_DTC, CommandType.Text, sql, parms))
                {
                    if (rdr.Read())
                    {
                        string wcode = rdr.GetString(0);
                        string wp = rdr.GetString(1);
                        string usql = "update womai_coupon set isapply=1,member_id=" + memberid + " where womai_code='" + wcode + "'"; 
                        int i = SQLHelper.ExecuteSql(usql, new SqlParameter[] { });
                        if (i > 0)
                        {
                            co = new string[] { wcode, wp };
                        }
                    }
                }
            }
            return co;
        }

        public bool UpdateCopon(string code)
        {
            string usql = "update womai_coupon set isapply=2 where womai_code='" + code + "'";
            int i = SQLHelper.ExecuteSql(usql, new SqlParameter[] { });
            if (i > 0)
            {
                return true;
            }
            return false;
        }
    }
}
